/*
Date: October 2025
Project: Income and Child Maltreatment: Evidence from a Discontinuity in Tax Benefits
Author: Katherine Rittenhouse
Purpose: This file creates a summary statistics table from CPS records
Files in: CPS_RefID; FCwide
Files out: sumstats_refch.tex (Table A1 in Online Supplementary Material)
*/


clear all
set more off

*panelcombine program from steveofconnell: github.com/steveofconnell/PanelCombine 
cap prog drop panelcombine
prog define panelcombine
qui {
syntax, use(str asis) paneltitles(str asis) columncount(integer) save(str asis) [CLEANup]
preserve

tokenize `"`paneltitles'"'
local num 1
while "``num''"~="" {
local panel`num'title="``num''"
local num=`num'+1
}


tokenize `use'
local num 1
while "``num''"~="" {
tempfile temp`num'
insheet using "``num''", clear
save `temp`num''
local max = `num'
local num=`num'+1
}

local num 1
while "``num''"~="" {
local panellabel : word `num' of `c(ALPHA)'
use `temp`num'', clear
	if `num'==1 { 
	drop if strpos(v1,"Note:")>0 | strpos(v1,"in parentheses")>0 | strpos(v1,"p<0")>0
	drop if v1=="\end{tabular}" | v1=="}"
	replace v1 = "\hline \multicolumn{`columncount'}{l}{ \linebreak \textbf{\textit{Panel `panellabel': `panel1title'}}} \\" if v1=="\hline" & _n<8
	replace v1 = "\hline" if v1=="\hline\hline" & _n>4 
	}
	else if `num'==`max' { 
	g temp = (v1 == "\hline")
	replace temp = temp+temp[_n-1] if _n>1
	drop if temp==0
	drop temp
	
	replace v1 = " \multicolumn{`columncount'}{l}{\linebreak \textbf{\textit{Panel `panellabel': `panel`num'title'}}} \\" if _n==1
	}
	else { 
	g temp = (v1 == "\hline")
	replace temp = temp+temp[_n-1] if _n>1
	drop if temp==0
	drop temp
	
	replace v1 = " \multicolumn{`columncount'}{l}{\linebreak \textbf{\textit{Panel `panellabel': `panel`num'title'}}} \\" if _n==1
	drop if strpos(v1,"Note:")>0 | strpos(v1,"in parentheses")>0 | strpos(v1,"p<0")>0
	drop if v1=="\end{tabular}" | v1=="}"
	replace v1 = "\hline" if v1=="\hline\hline"
	}
	save `temp`num'', replace
local num=`num'+1
}

use `temp1',clear
local num 2
while "``num''"~="" {
append using `temp`num''
local num=`num'+1
}

outsheet using `save', noname replace noquote


	if "`cleanup'"!="" { 
	tokenize `use'
	local num 1
		while "``num''"~="" {
		erase "``num''"
		local num=`num'+1
		}
	}

restore
}
end

*********************

use "CPS_RefID",clear
ren fkclient FKCLIENT_T

merge m:1 FKCLIENT_T using "FCwide"

drop if _m==2
drop _m

keep if age_at_referral>=0 & age_at_referral<=2

gen refcat = "other"
*nonmand - aunt, brother, cousin, grandparent, great aunt/uncle, great grandparent, relative, sister, uncle, friend, neighbor, godparent, landlord, no relation
replace refcat = "nonmand" if COL_RELC == 572
replace refcat = "nonmand" if COL_RELC == 573
replace refcat = "nonmand" if COL_RELC == 577
replace refcat = "nonmand" if COL_RELC == 583
replace refcat = "nonmand" if COL_RELC == 584
replace refcat = "nonmand" if COL_RELC == 585
replace refcat = "nonmand" if COL_RELC == 586
replace refcat = "nonmand" if COL_RELC == 596
replace refcat = "nonmand" if COL_RELC == 598
replace refcat = "nonmand" if COL_RELC == 601
replace refcat = "nonmand" if COL_RELC ==580
replace refcat = "nonmand" if COL_RELC ==591
replace refcat = "nonmand" if COL_RELC ==581
replace refcat = "nonmand" if COL_RELC ==588
replace refcat = "nonmand" if COL_RELC ==592

*school - school personnel, teacher, 
replace refcat = "school" if COL_RELC == 597
replace refcat = "school" if COL_RELC == 600


*medical professional - doctor, counselor, dentist
replace refcat = "medprof" if COL_RELC ==590
replace refcat = "medprof" if COL_RELC ==576
replace refcat = "medprof" if COL_RELC ==5924


*legal professional - law enforcement, parole officer, probation officer
replace refcat = "legprof" if COL_RELC ==589
replace refcat = "legprof" if COL_RELC ==595
replace refcat = "legprof" if COL_RELC ==594

*cws staff
replace refcat = "cws" if COL_RELC ==578


keep if cps_birthy>=1999 & cps_birthy<2020

gen FosterCare90days = 0
gen FosterCare180days = 0


forval x = 1/11 {	
	replace FosterCare90days = 1 if PE_S_DT`x' >= REF_RCV & PE_S_DT`x'<= REF_RCV+90 & REF_RCV!=. & PE_S_DT`x'!=.
	replace FosterCare180days = 1 if PE_S_DT`x' >= REF_RCV & PE_S_DT`x'<= REF_RCV+180 & REF_RCV!=. & PE_S_DT`x'!=.

	}


gen NonMandated =  refcat=="nonmand"
gen School =  refcat == "school"
gen LawEnforcement =  refcat == "legprof"
gen Medical =  refcat == "medprof"
gen CWS = refcat == "cws"
gen OtherReporter = refcat == "other"


ren alleg_neglect Neglect
ren alleg_physical Physical
ren alleg_emotional Emotional
ren alleg_other OtherAllegation

gen AnySubstantiated = max(subst_neglect, subst_physical, subst_emotional, subst_other)
gen AnyInconclusive = max(indic_neglect, indic_physical, indic_emotional, indic_other)

gen Investigated = (invest==1)

label var NonMandated "Non-Mandated"
label var School "School"
label var LawEnforcement "Law Enforcement"
label var CWS "CPS Worker"
label var OtherReporter "Other Reporter"
label var Neglect "Neglect"
label var Physical "Physical Abuse"
label var Emotional "Emotional Abuse"
label var OtherAllegation "Other Allegation"
label var AnySubstantiated "Any Substantiated Allegation"
label var AnyInconclusive "Any Inconclusive Allegation"
label var Investigated "Investigated Referral"
label var FosterCare90days "Placed in Foster Care within 90 Days"

eststo clear

estpost sum NonMandated School LawEnforcement Medical CWS OtherReporter 
est store panela
esttab using "panela.tex", replace noobs mlabels(none) collabels(\multcolumn{{Mean}}) cells("mean(fmt(3))")

estpost sum Neglect Physical Emotional OtherAllegation 
est store panelb
esttab using "panelb.tex", replace noobs mlabels(none) collabels(\multcolumn{{Mean}}) cells("mean(fmt(3))")

estpost sum Investigated AnySubstantiated AnyInconclusive FosterCare90days 
est store panelc
esttab using "panelc.tex", replace  mlabels(none) collabels(\multcolumn{{Mean}}) cells("mean(fmt(3))")


cd "summstats"
panelcombine, use(panela.tex panelb.tex panelc.tex) columncount(5) paneltitles("Reporter category" "Allegation category" "Outcomes") save(sumstats_refch.tex) 


